package com.junzhiit.nengji.dao.impl;

import java.util.List;

import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Repository;

import com.bainaeco.framework.base.dao.AbstractBaseDAO;
import com.bainaeco.framework.common.jdbc.StatementParameter;
import com.bainaeco.framework.enums.IsDelete;
import com.junzhiit.nengji.bo.ShopClientBO;
import com.junzhiit.nengji.dao.ShopClientDAO;
import com.junzhiit.nengji.model.ShopClient;
import com.junzhiit.nengji.model.enums.Is;
import com.junzhiit.nengji.model.enums.OrderStatus;

@Repository
public class ShopClientDAOImpl extends AbstractBaseDAO<ShopClient> implements ShopClientDAO{

	@Override
	public List<ShopClientBO> findAll(ShopClientBO shopClientBO,int pageNumber, int maxPageRow) {
		StringBuffer sql = new StringBuffer();
		StatementParameter param = new StatementParameter();
		sql.append("select s.*,m.card_name as memberCardName,u.nick_name,u.mobile_phone,");
		sql.append("(select count(id) from orders o where o.user_id = s.user_id and o.status = ?) 'buy_count',");
		sql.append("(select ifnull(sum(pay_money),0.00) from orders o where o.user_id = s.user_id and o.status = ?) 'consume_price'");
		sql.append(" from shop_client s");
		sql.append(" inner join user u on u.id = s.user_id");
		sql.append(" left join member_card m on s.member_card_id = m.id");
		sql.append(" where s.cs_id=?");
		param.setInt(OrderStatus.COMPLETE.toIntValue());
		param.setInt(OrderStatus.COMPLETE.toIntValue());
		param.setString(shopClientBO.getCsId());
		
		if (shopClientBO.getClientStatus() != null) {
			sql.append(" and s.client_status = ?");
			param.setInt(shopClientBO.getClientStatus());
		}
		if(StringUtils.isNotBlank(shopClientBO.getMobilePhone())){
			sql.append(" and u.mobile_phone like ?");
			param.setString("%"+shopClientBO.getMobilePhone()+"%");
		}
		if (StringUtils.isNotBlank(shopClientBO.getMemberCardId())) {
			sql.append(" and s.member_card_id = ?");
			param.setString(shopClientBO.getMemberCardId());
		}
		if(StringUtils.isNotBlank(shopClientBO.getCardNumber())){
			sql.append(" and s.card_number like ?");
			param.setString("%"+shopClientBO.getCardNumber()+"%");
		}
		
		sql.append(" group by s.id");
		
		return queryForPagingList(sql.toString(), ShopClientBO.class, param,pageNumber,maxPageRow);
	}
	
	@Override
	public int getTotalCount(ShopClientBO shopClientBO) {
		StringBuffer sql = new StringBuffer();
		sql.append("select count(s.id) from shop_client s");
		sql.append(" inner join user u on u.id = s.user_id");
		sql.append(" where s.cs_id=?");
		StatementParameter param = new StatementParameter();
		
		param.setString(shopClientBO.getCsId());
		
		if (shopClientBO.getClientStatus() != null) {
			sql.append(" and s.client_status = ?");
			param.setInt(shopClientBO.getClientStatus());
		}
		
		if(StringUtils.isNotBlank(shopClientBO.getMobilePhone())){
			sql.append(" and u.mobile_phone like ?");
			param.setString("%"+shopClientBO.getMobilePhone()+"%");
		}
		
		if(StringUtils.isNotBlank(shopClientBO.getCardNumber())){
			sql.append(" and s.card_number like ?");
			param.setString("%"+shopClientBO.getCardNumber()+"%");
		}
		
		if (shopClientBO.getMemberCardId() != null) {
			sql.append(" and s.member_card_id = ?");
			param.setString(shopClientBO.getMemberCardId());
		}
		return queryForInt(sql.toString(), param);
	}
	
	@Override
	public List<ShopClientBO> findAll(ShopClientBO shopClientBO) {
		StringBuffer sql = new StringBuffer();
		StatementParameter param = new StatementParameter();
		sql.append("select s.*,m.card_name as memberCardName,u.nick_name,u.mobile_phone,");
		sql.append("(select count(id) from orders o where o.user_id = s.user_id and o.status = ?) 'buy_count',");
		sql.append("(select ifnull(sum(pay_money),0.00) from orders o where o.user_id = s.user_id and o.status = ?) 'consume_price'");
		sql.append(" from shop_client s");
		sql.append(" inner join user u on u.id = s.user_id");
		sql.append(" left join member_card m on s.member_card_id = m.id");
		sql.append(" where (s.member_card_id is not null or s.member_card_id !=null) and s.cs_id=?");
		param.setInt(OrderStatus.COMPLETE.toIntValue());
		param.setInt(OrderStatus.COMPLETE.toIntValue());
		param.setString(shopClientBO.getCsId());
		
		if(StringUtils.isNotBlank(shopClientBO.getUserId())){
			sql.append(" and s.user_id = ?");
			param.setString(shopClientBO.getUserId());
		}
		if (shopClientBO.getClientStatus() != null) {
			sql.append(" and s.client_status = ?");
			param.setInt(shopClientBO.getClientStatus());
		}
		if(StringUtils.isNotBlank(shopClientBO.getMobilePhone())){
			sql.append(" and u.mobile_phone like ?");
			param.setString("%"+shopClientBO.getMobilePhone()+"%");
		}
		if (StringUtils.isNotBlank(shopClientBO.getMemberCardId())) {
			sql.append(" and s.member_card_id = ?");
			param.setString(shopClientBO.getMemberCardId());
		}
		if(StringUtils.isNotBlank(shopClientBO.getCardNumber())){
			sql.append(" and s.card_number like ?");
			param.setString("%"+shopClientBO.getCardNumber()+"%");
		}
		
		sql.append(" and s.is_delete= ? ");
		param.setInt(IsDelete.NO.toIntValue());
		
		sql.append(" group by s.id");
		
		return queryForList(sql.toString(), ShopClientBO.class, param);

	}


	@Override
	public ShopClient findClientByUserId(String userId,String csId) {
		StringBuffer sql = new StringBuffer();
		sql.append("select * from shop_client where user_id = ? and cs_id = ? and is_delete = ? ");

		StatementParameter param = new StatementParameter();
		param.setString(userId);
		param.setString(csId);
		param.setInt(IsDelete.NO.toIntValue());
		return super.query(sql.toString(),ShopClient.class,param);
	}

	@Override
	public ShopClient findByNumber(String csId,String cardNumber) {
		StringBuffer sql = new StringBuffer();
		sql.append("select * from shop_client where cs_id = ? and card_number = ? and is_delete = ? ");

		StatementParameter param = new StatementParameter();
		param.setString(csId);
		param.setString(cardNumber);
		param.setInt(IsDelete.NO.toIntValue());
		return super.query(sql.toString(),ShopClient.class,param);
	}
	
	@Override
	public ShopClientBO details(String csId,String userId){
		StringBuffer sql = new StringBuffer();
		StatementParameter param = new StatementParameter();
		sql.append("select s.*,m.card_name as memberCardName,u.nick_name,u.mobile_phone,");
		sql.append("(select count(id) from orders o where o.user_id = s.user_id and o.status = ?) 'buy_count',");
		sql.append("(select ifnull(sum(pay_money),0.00) from orders o where o.user_id = s.user_id and o.status = ?) 'consume_price'");
		sql.append(" from shop_client s");
		sql.append(" inner join user u on u.id = s.user_id");
		sql.append(" left join member_card m on s.member_card_id = m.id");
		sql.append(" where s.cs_id=? and s.user_id = ? and s.is_delete = ? ");
		param.setInt(OrderStatus.COMPLETE.toIntValue());
		param.setInt(OrderStatus.COMPLETE.toIntValue());
		param.setString(csId);
		param.setString(userId);
		param.setInt(IsDelete.NO.toIntValue());
		return super.query(sql.toString(),ShopClientBO.class,param);
	}

	@Override
	public List<ShopClient> findShopClient() {
		StringBuffer sql = new StringBuffer();
		StatementParameter param = new StatementParameter();
		sql.append("select * from shop_client where client_status = ? and is_delete = ?");
		sql.append(" and member_end_date < now()");
		param.setInt(Is.YES.toIntValue());
		param.setInt(Is.NO.toIntValue());
		return queryForList(sql.toString(), ShopClient.class, param);
	}
	
}
